{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "434a21db-5bf1-46e5-b349-1a9b4c596c8f",
   "metadata": {},
   "source": [
    "This Jupyter Notebook contains the code used in the [RealPython]()https://www.realpython.com) tutorial [**Aggregating and Grouping Data in Polars**](https://realpython.com/aggregating-and-grouping-data-in-polars-groupby/)\n",
    "\n",
    "# Aggregating Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a8613c94-93ef-4eeb-a916-06728100a352",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9ddb39eb-e50b-4678-8fb0-d65ab8269e0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>absences</th></tr><tr><td>i64</td></tr></thead><tbody><tr><td>75</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 1)\n",
       "┌──────────┐\n",
       "│ absences │\n",
       "│ ---      │\n",
       "│ i64      │\n",
       "╞══════════╡\n",
       "│ 75       │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(math_students.select(pl.col(\"absences\").max()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32217883-4097-4ddf-a5df-cdc838e1b5f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>student_id</th><th>absences</th></tr><tr><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>10277</td><td>75</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 2)\n",
       "┌────────────┬──────────┐\n",
       "│ student_id ┆ absences │\n",
       "│ ---        ┆ ---      │\n",
       "│ i64        ┆ i64      │\n",
       "╞════════════╪══════════╡\n",
       "│ 10277      ┆ 75       │\n",
       "└────────────┴──────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.select(pl.col(\"student_id\", \"absences\")).filter(\n",
    "        pl.col(\"absences\") == pl.col(\"absences\").max()\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "60236723-fb40-477b-8305-22c604cff985",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>min</th><th>max</th><th>mean</th></tr><tr><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>75</td><td>5.708861</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 3)\n",
       "┌─────┬─────┬──────────┐\n",
       "│ min ┆ max ┆ mean     │\n",
       "│ --- ┆ --- ┆ ---      │\n",
       "│ i64 ┆ i64 ┆ f64      │\n",
       "╞═════╪═════╪══════════╡\n",
       "│ 0   ┆ 75  ┆ 5.708861 │\n",
       "└─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.select(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ee5dcc39-e45e-4e32-890c-90303cf5de04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (395, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>student_id</th><th>G1</th><th>G2</th><th>G3</th><th>total</th><th>mean</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>10001</td><td>5</td><td>6</td><td>6</td><td>17</td><td>5.666667</td></tr><tr><td>10002</td><td>5</td><td>5</td><td>6</td><td>16</td><td>5.333333</td></tr><tr><td>10003</td><td>7</td><td>8</td><td>10</td><td>25</td><td>8.333333</td></tr><tr><td>10004</td><td>15</td><td>14</td><td>15</td><td>44</td><td>14.666667</td></tr><tr><td>10005</td><td>6</td><td>10</td><td>10</td><td>26</td><td>8.666667</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>10391</td><td>9</td><td>9</td><td>9</td><td>27</td><td>9.0</td></tr><tr><td>10392</td><td>14</td><td>16</td><td>16</td><td>46</td><td>15.333333</td></tr><tr><td>10393</td><td>10</td><td>8</td><td>7</td><td>25</td><td>8.333333</td></tr><tr><td>10394</td><td>11</td><td>12</td><td>10</td><td>33</td><td>11.0</td></tr><tr><td>10395</td><td>8</td><td>9</td><td>9</td><td>26</td><td>8.666667</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (395, 6)\n",
       "┌────────────┬─────┬─────┬─────┬───────┬───────────┐\n",
       "│ student_id ┆ G1  ┆ G2  ┆ G3  ┆ total ┆ mean      │\n",
       "│ ---        ┆ --- ┆ --- ┆ --- ┆ ---   ┆ ---       │\n",
       "│ i64        ┆ i64 ┆ i64 ┆ i64 ┆ i64   ┆ f64       │\n",
       "╞════════════╪═════╪═════╪═════╪═══════╪═══════════╡\n",
       "│ 10001      ┆ 5   ┆ 6   ┆ 6   ┆ 17    ┆ 5.666667  │\n",
       "│ 10002      ┆ 5   ┆ 5   ┆ 6   ┆ 16    ┆ 5.333333  │\n",
       "│ 10003      ┆ 7   ┆ 8   ┆ 10  ┆ 25    ┆ 8.333333  │\n",
       "│ 10004      ┆ 15  ┆ 14  ┆ 15  ┆ 44    ┆ 14.666667 │\n",
       "│ 10005      ┆ 6   ┆ 10  ┆ 10  ┆ 26    ┆ 8.666667  │\n",
       "│ …          ┆ …   ┆ …   ┆ …   ┆ …     ┆ …         │\n",
       "│ 10391      ┆ 9   ┆ 9   ┆ 9   ┆ 27    ┆ 9.0       │\n",
       "│ 10392      ┆ 14  ┆ 16  ┆ 16  ┆ 46    ┆ 15.333333 │\n",
       "│ 10393      ┆ 10  ┆ 8   ┆ 7   ┆ 25    ┆ 8.333333  │\n",
       "│ 10394      ┆ 11  ┆ 12  ┆ 10  ┆ 33    ┆ 11.0      │\n",
       "│ 10395      ┆ 8   ┆ 9   ┆ 9   ┆ 26    ┆ 8.666667  │\n",
       "└────────────┴─────┴─────┴─────┴───────┴───────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "math_students.select(\n",
    "    \"student_id\",\n",
    "    \"G1\",\n",
    "    \"G2\",\n",
    "    \"G3\",\n",
    "    total=pl.sum_horizontal(\"G1\", \"G2\", \"G3\"),\n",
    "    mean=pl.mean_horizontal(\"G1\", \"G2\", \"G3\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cf8d3a6-5612-4734-ab3f-83bb1d032bfc",
   "metadata": {},
   "source": [
    "# Grouping Data for Aggregation With .groupby()\n",
    "## Grouping Data - Basic Principles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "22537c77-d14e-4e23-bcc5-9aa63e311d70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>min</th><th>max</th><th>mean</th></tr><tr><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>75</td><td>5.708861</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 3)\n",
       "┌─────┬─────┬──────────┐\n",
       "│ min ┆ max ┆ mean     │\n",
       "│ --- ┆ --- ┆ ---      │\n",
       "│ i64 ┆ i64 ┆ f64      │\n",
       "╞═════╪═════╪══════════╡\n",
       "│ 0   ┆ 75  ┆ 5.708861 │\n",
       "└─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.select(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "17cb8dcf-6329-4b93-871d-86645abb2ee8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>min</th><th>max</th><th>mean</th></tr><tr><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>32</td><td>3.659476</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 3)\n",
       "┌─────┬─────┬──────────┐\n",
       "│ min ┆ max ┆ mean     │\n",
       "│ --- ┆ --- ┆ ---      │\n",
       "│ i64 ┆ i64 ┆ f64      │\n",
       "╞═════╪═════╪══════════╡\n",
       "│ 0   ┆ 32  ┆ 3.659476 │\n",
       "└─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portuguese_students = pl.read_parquet(\"portuguese.parquet\")\n",
    "\n",
    "(\n",
    "    portuguese_students.select(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "87b84eba-5691-4d42-ada1-20dd9553c9da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>min</th><th>max</th><th>mean</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>0</td><td>75</td><td>5.708861</td></tr><tr><td>&quot;P&quot;</td><td>0</td><td>32</td><td>3.659476</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 4)\n",
       "┌─────────┬─────┬─────┬──────────┐\n",
       "│ subject ┆ min ┆ max ┆ mean     │\n",
       "│ ---     ┆ --- ┆ --- ┆ ---      │\n",
       "│ str     ┆ i64 ┆ i64 ┆ f64      │\n",
       "╞═════════╪═════╪═════╪══════════╡\n",
       "│ M       ┆ 0   ┆ 75  ┆ 5.708861 │\n",
       "│ P       ┆ 0   ┆ 32  ┆ 3.659476 │\n",
       "└─────────┴─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.group_by(\"subject\").agg(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a1427c4-8c5a-4df4-ae0a-c393c2aefbad",
   "metadata": {},
   "source": [
    "## Grouping Data - The Power of Expressions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2690faf4-ad0b-4811-8660-1ca444b9bf9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>age</th><th>passes</th></tr><tr><td>i64</td><td>list[i64]</td></tr></thead><tbody><tr><td>18</td><td>[6, 0, … 10]</td></tr><tr><td>20</td><td>[18, 15, 9]</td></tr><tr><td>15</td><td>[10, 15, … 7]</td></tr><tr><td>17</td><td>[6, 6, … 16]</td></tr><tr><td>16</td><td>[10, 15, … 8]</td></tr><tr><td>19</td><td>[9, 0, … 9]</td></tr><tr><td>22</td><td>[8]</td></tr><tr><td>21</td><td>[7]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 2)\n",
       "┌─────┬───────────────┐\n",
       "│ age ┆ passes        │\n",
       "│ --- ┆ ---           │\n",
       "│ i64 ┆ list[i64]     │\n",
       "╞═════╪═══════════════╡\n",
       "│ 18  ┆ [6, 0, … 10]  │\n",
       "│ 20  ┆ [18, 15, 9]   │\n",
       "│ 15  ┆ [10, 15, … 7] │\n",
       "│ 17  ┆ [6, 6, … 16]  │\n",
       "│ 16  ┆ [10, 15, … 8] │\n",
       "│ 19  ┆ [9, 0, … 9]   │\n",
       "│ 22  ┆ [8]           │\n",
       "│ 21  ┆ [7]           │\n",
       "└─────┴───────────────┘"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.group_by(\"age\").agg(\n",
    "        passes=pl.col(\"G3\"),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "123401a9-20f4-40ca-b32a-a48850d1cd23",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>age</th><th>passes</th></tr><tr><td>i64</td><td>list[i64]</td></tr></thead><tbody><tr><td>16</td><td>[15, 14, … 18]</td></tr><tr><td>19</td><td>[13, 13, … 13]</td></tr><tr><td>21</td><td>[]</td></tr><tr><td>18</td><td>[14, 18, … 13]</td></tr><tr><td>20</td><td>[]</td></tr><tr><td>22</td><td>[]</td></tr><tr><td>15</td><td>[15, 13, … 15]</td></tr><tr><td>17</td><td>[13, 18, … 15]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 2)\n",
       "┌─────┬────────────────┐\n",
       "│ age ┆ passes         │\n",
       "│ --- ┆ ---            │\n",
       "│ i64 ┆ list[i64]      │\n",
       "╞═════╪════════════════╡\n",
       "│ 16  ┆ [15, 14, … 18] │\n",
       "│ 19  ┆ [13, 13, … 13] │\n",
       "│ 21  ┆ []             │\n",
       "│ 18  ┆ [14, 18, … 13] │\n",
       "│ 20  ┆ []             │\n",
       "│ 22  ┆ []             │\n",
       "│ 15  ┆ [15, 13, … 15] │\n",
       "│ 17  ┆ [13, 18, … 15] │\n",
       "└─────┴────────────────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.group_by(\"age\").agg(\n",
    "        passes=pl.col(\"G3\").filter(\n",
    "            pl.col(\"absences\") > pl.col(\"absences\").mean(), pl.col(\"G3\") >= 13\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "02146ea3-03f1-4150-ad50-e1e6f610db34",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>age</th><th>passes</th><th>poor_attenders</th><th>percentage</th></tr><tr><td>i64</td><td>u32</td><td>u32</td><td>f64</td></tr></thead><tbody><tr><td>15</td><td>15</td><td>32</td><td>46.875</td></tr><tr><td>16</td><td>11</td><td>39</td><td>28.205128</td></tr><tr><td>17</td><td>8</td><td>29</td><td>27.586207</td></tr><tr><td>18</td><td>11</td><td>31</td><td>35.483871</td></tr><tr><td>19</td><td>4</td><td>10</td><td>40.0</td></tr><tr><td>20</td><td>0</td><td>1</td><td>0.0</td></tr><tr><td>21</td><td>0</td><td>0</td><td>0.0</td></tr><tr><td>22</td><td>0</td><td>0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 4)\n",
       "┌─────┬────────┬────────────────┬────────────┐\n",
       "│ age ┆ passes ┆ poor_attenders ┆ percentage │\n",
       "│ --- ┆ ---    ┆ ---            ┆ ---        │\n",
       "│ i64 ┆ u32    ┆ u32            ┆ f64        │\n",
       "╞═════╪════════╪════════════════╪════════════╡\n",
       "│ 15  ┆ 15     ┆ 32             ┆ 46.875     │\n",
       "│ 16  ┆ 11     ┆ 39             ┆ 28.205128  │\n",
       "│ 17  ┆ 8      ┆ 29             ┆ 27.586207  │\n",
       "│ 18  ┆ 11     ┆ 31             ┆ 35.483871  │\n",
       "│ 19  ┆ 4      ┆ 10             ┆ 40.0       │\n",
       "│ 20  ┆ 0      ┆ 1              ┆ 0.0        │\n",
       "│ 21  ┆ 0      ┆ 0              ┆ 0.0        │\n",
       "│ 22  ┆ 0      ┆ 0              ┆ 0.0        │\n",
       "└─────┴────────┴────────────────┴────────────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_students = pl.read_parquet(\"math.parquet\")\n",
    "\n",
    "(\n",
    "    math_students.group_by(\"age\")\n",
    "    .agg(\n",
    "        passes=pl.col(\"G3\")\n",
    "        .filter(\n",
    "            pl.col(\"absences\") > pl.col(\"absences\").mean(), pl.col(\"G3\") >= 13\n",
    "        )\n",
    "        .count(),\n",
    "        poor_attenders=pl.col(\"G3\")\n",
    "        .filter(pl.col(\"absences\") > pl.col(\"absences\").mean())\n",
    "        .count(),\n",
    "    )\n",
    "    .select(\n",
    "        pl.col(\"age\", \"passes\", \"poor_attenders\"),\n",
    "        percentage=pl.col(\"passes\") / pl.col(\"poor_attenders\") * 100,\n",
    "    )\n",
    "    .with_columns(pl.col(\"percentage\").replace(float(\"NaN\"), 0))\n",
    "    .sort(\"age\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccd1477a-0afa-4972-a9c7-309ac5220943",
   "metadata": {},
   "source": [
    "# Grouping and Aggregating by Multiple Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2a7f6224-8fe3-4d12-b65f-be68af1c1639",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>reason</th><th>min</th><th>max</th><th>mean</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>56</td><td>6.647619</td></tr><tr><td>&quot;M&quot;</td><td>&quot;course&quot;</td><td>0</td><td>23</td><td>3.972414</td></tr><tr><td>&quot;M&quot;</td><td>&quot;other&quot;</td><td>0</td><td>20</td><td>5.611111</td></tr><tr><td>&quot;M&quot;</td><td>&quot;home&quot;</td><td>0</td><td>75</td><td>7.146789</td></tr><tr><td>&quot;P&quot;</td><td>&quot;home&quot;</td><td>0</td><td>30</td><td>4.456376</td></tr><tr><td>&quot;P&quot;</td><td>&quot;other&quot;</td><td>0</td><td>16</td><td>2.777778</td></tr><tr><td>&quot;P&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>32</td><td>3.811189</td></tr><tr><td>&quot;P&quot;</td><td>&quot;course&quot;</td><td>0</td><td>26</td><td>3.389474</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 5)\n",
       "┌─────────┬────────────┬─────┬─────┬──────────┐\n",
       "│ subject ┆ reason     ┆ min ┆ max ┆ mean     │\n",
       "│ ---     ┆ ---        ┆ --- ┆ --- ┆ ---      │\n",
       "│ str     ┆ str        ┆ i64 ┆ i64 ┆ f64      │\n",
       "╞═════════╪════════════╪═════╪═════╪══════════╡\n",
       "│ M       ┆ reputation ┆ 0   ┆ 56  ┆ 6.647619 │\n",
       "│ M       ┆ course     ┆ 0   ┆ 23  ┆ 3.972414 │\n",
       "│ M       ┆ other      ┆ 0   ┆ 20  ┆ 5.611111 │\n",
       "│ M       ┆ home       ┆ 0   ┆ 75  ┆ 7.146789 │\n",
       "│ P       ┆ home       ┆ 0   ┆ 30  ┆ 4.456376 │\n",
       "│ P       ┆ other      ┆ 0   ┆ 16  ┆ 2.777778 │\n",
       "│ P       ┆ reputation ┆ 0   ┆ 32  ┆ 3.811189 │\n",
       "│ P       ┆ course     ┆ 0   ┆ 26  ┆ 3.389474 │\n",
       "└─────────┴────────────┴─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.group_by([\"subject\", \"reason\"])\n",
    "    .agg(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    "    .sort(\"subject\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b8b0cc6f-3628-4f71-b7ac-b1fa246b830d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>reason</th><th>min</th><th>max</th><th>mean</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;course&quot;</td><td>0</td><td>23</td><td>3.972414</td></tr><tr><td>&quot;M&quot;</td><td>&quot;home&quot;</td><td>0</td><td>75</td><td>7.146789</td></tr><tr><td>&quot;M&quot;</td><td>&quot;other&quot;</td><td>0</td><td>20</td><td>5.611111</td></tr><tr><td>&quot;M&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>56</td><td>6.647619</td></tr><tr><td>&quot;P&quot;</td><td>&quot;course&quot;</td><td>0</td><td>26</td><td>3.389474</td></tr><tr><td>&quot;P&quot;</td><td>&quot;home&quot;</td><td>0</td><td>30</td><td>4.456376</td></tr><tr><td>&quot;P&quot;</td><td>&quot;other&quot;</td><td>0</td><td>16</td><td>2.777778</td></tr><tr><td>&quot;P&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>32</td><td>3.811189</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 5)\n",
       "┌─────────┬────────────┬─────┬─────┬──────────┐\n",
       "│ subject ┆ reason     ┆ min ┆ max ┆ mean     │\n",
       "│ ---     ┆ ---        ┆ --- ┆ --- ┆ ---      │\n",
       "│ str     ┆ str        ┆ i64 ┆ i64 ┆ f64      │\n",
       "╞═════════╪════════════╪═════╪═════╪══════════╡\n",
       "│ M       ┆ course     ┆ 0   ┆ 23  ┆ 3.972414 │\n",
       "│ M       ┆ home       ┆ 0   ┆ 75  ┆ 7.146789 │\n",
       "│ M       ┆ other      ┆ 0   ┆ 20  ┆ 5.611111 │\n",
       "│ M       ┆ reputation ┆ 0   ┆ 56  ┆ 6.647619 │\n",
       "│ P       ┆ course     ┆ 0   ┆ 26  ┆ 3.389474 │\n",
       "│ P       ┆ home       ┆ 0   ┆ 30  ┆ 4.456376 │\n",
       "│ P       ┆ other      ┆ 0   ┆ 16  ┆ 2.777778 │\n",
       "│ P       ┆ reputation ┆ 0   ┆ 32  ┆ 3.811189 │\n",
       "└─────────┴────────────┴─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.group_by([\"subject\", \"reason\"])\n",
    "    .agg(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    "    .sort(\"subject\", \"reason\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "42a17b9e-ac13-4110-8c95-a66c4c66c3a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>reason</th><th>min</th><th>max</th><th>mean</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>56</td><td>6.647619</td></tr><tr><td>&quot;M&quot;</td><td>&quot;other&quot;</td><td>0</td><td>20</td><td>5.611111</td></tr><tr><td>&quot;M&quot;</td><td>&quot;home&quot;</td><td>0</td><td>75</td><td>7.146789</td></tr><tr><td>&quot;M&quot;</td><td>&quot;course&quot;</td><td>0</td><td>23</td><td>3.972414</td></tr><tr><td>&quot;P&quot;</td><td>&quot;reputation&quot;</td><td>0</td><td>32</td><td>3.811189</td></tr><tr><td>&quot;P&quot;</td><td>&quot;other&quot;</td><td>0</td><td>16</td><td>2.777778</td></tr><tr><td>&quot;P&quot;</td><td>&quot;home&quot;</td><td>0</td><td>30</td><td>4.456376</td></tr><tr><td>&quot;P&quot;</td><td>&quot;course&quot;</td><td>0</td><td>26</td><td>3.389474</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 5)\n",
       "┌─────────┬────────────┬─────┬─────┬──────────┐\n",
       "│ subject ┆ reason     ┆ min ┆ max ┆ mean     │\n",
       "│ ---     ┆ ---        ┆ --- ┆ --- ┆ ---      │\n",
       "│ str     ┆ str        ┆ i64 ┆ i64 ┆ f64      │\n",
       "╞═════════╪════════════╪═════╪═════╪══════════╡\n",
       "│ M       ┆ reputation ┆ 0   ┆ 56  ┆ 6.647619 │\n",
       "│ M       ┆ other      ┆ 0   ┆ 20  ┆ 5.611111 │\n",
       "│ M       ┆ home       ┆ 0   ┆ 75  ┆ 7.146789 │\n",
       "│ M       ┆ course     ┆ 0   ┆ 23  ┆ 3.972414 │\n",
       "│ P       ┆ reputation ┆ 0   ┆ 32  ┆ 3.811189 │\n",
       "│ P       ┆ other      ┆ 0   ┆ 16  ┆ 2.777778 │\n",
       "│ P       ┆ home       ┆ 0   ┆ 30  ┆ 4.456376 │\n",
       "│ P       ┆ course     ┆ 0   ┆ 26  ┆ 3.389474 │\n",
       "└─────────┴────────────┴─────┴─────┴──────────┘"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.group_by([\"subject\", \"reason\"])\n",
    "    .agg(\n",
    "        min=pl.col(\"absences\").min(),\n",
    "        max=pl.col(\"absences\").max(),\n",
    "        mean=pl.col(\"absences\").mean(),\n",
    "    )\n",
    "    .sort(\"reason\", descending=True)\n",
    "    .sort(\"subject\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fff08ed0-de4e-40b5-a43a-f65804ec9aae",
   "metadata": {},
   "source": [
    "# Grouping and Aggregating Time Series With .group_by_dynamic()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "87157b1f-f9d3-4a8e-b0f7-76e41803d73a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>class_start</th><th>class_subject</th><th>absences</th><th>lecturer_initials</th></tr><tr><td>datetime[μs]</td><td>str</td><td>i64</td><td>str</td></tr></thead><tbody><tr><td>2024-01-03 09:00:00</td><td>&quot;algebra&quot;</td><td>3</td><td>&quot;DH&quot;</td></tr><tr><td>2024-01-04 13:30:00</td><td>&quot;geometry&quot;</td><td>4</td><td>&quot;PS&quot;</td></tr><tr><td>2024-01-05 10:00:00</td><td>&quot;calculus&quot;</td><td>3</td><td>&quot;LM&quot;</td></tr><tr><td>2024-01-10 09:00:00</td><td>&quot;algebra&quot;</td><td>2</td><td>&quot;DH&quot;</td></tr><tr><td>2024-01-11 13:30:00</td><td>&quot;geometry&quot;</td><td>7</td><td>&quot;PS&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 4)\n",
       "┌─────────────────────┬───────────────┬──────────┬───────────────────┐\n",
       "│ class_start         ┆ class_subject ┆ absences ┆ lecturer_initials │\n",
       "│ ---                 ┆ ---           ┆ ---      ┆ ---               │\n",
       "│ datetime[μs]        ┆ str           ┆ i64      ┆ str               │\n",
       "╞═════════════════════╪═══════════════╪══════════╪═══════════════════╡\n",
       "│ 2024-01-03 09:00:00 ┆ algebra       ┆ 3        ┆ DH                │\n",
       "│ 2024-01-04 13:30:00 ┆ geometry      ┆ 4        ┆ PS                │\n",
       "│ 2024-01-05 10:00:00 ┆ calculus      ┆ 3        ┆ LM                │\n",
       "│ 2024-01-10 09:00:00 ┆ algebra       ┆ 2        ┆ DH                │\n",
       "│ 2024-01-11 13:30:00 ┆ geometry      ┆ 7        ┆ PS                │\n",
       "└─────────────────────┴───────────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "math_attendance = pl.read_parquet(\"math_classes.parquet\")\n",
    "math_attendance.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "af32bd7a-fade-4c88-b7c1-be96863f5614",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (26, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>class_start</th><th>total_absences</th><th>mean_absences</th></tr><tr><td>datetime[μs]</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>2024-01-01 00:00:00</td><td>10</td><td>3.333333</td></tr><tr><td>2024-01-08 00:00:00</td><td>10</td><td>3.333333</td></tr><tr><td>2024-01-15 00:00:00</td><td>11</td><td>3.666667</td></tr><tr><td>2024-01-22 00:00:00</td><td>10</td><td>3.333333</td></tr><tr><td>2024-01-29 00:00:00</td><td>4</td><td>1.333333</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2024-05-27 00:00:00</td><td>15</td><td>5.0</td></tr><tr><td>2024-06-03 00:00:00</td><td>12</td><td>4.0</td></tr><tr><td>2024-06-10 00:00:00</td><td>17</td><td>5.666667</td></tr><tr><td>2024-06-17 00:00:00</td><td>6</td><td>2.0</td></tr><tr><td>2024-06-24 00:00:00</td><td>14</td><td>4.666667</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (26, 3)\n",
       "┌─────────────────────┬────────────────┬───────────────┐\n",
       "│ class_start         ┆ total_absences ┆ mean_absences │\n",
       "│ ---                 ┆ ---            ┆ ---           │\n",
       "│ datetime[μs]        ┆ i64            ┆ f64           │\n",
       "╞═════════════════════╪════════════════╪═══════════════╡\n",
       "│ 2024-01-01 00:00:00 ┆ 10             ┆ 3.333333      │\n",
       "│ 2024-01-08 00:00:00 ┆ 10             ┆ 3.333333      │\n",
       "│ 2024-01-15 00:00:00 ┆ 11             ┆ 3.666667      │\n",
       "│ 2024-01-22 00:00:00 ┆ 10             ┆ 3.333333      │\n",
       "│ 2024-01-29 00:00:00 ┆ 4              ┆ 1.333333      │\n",
       "│ …                   ┆ …              ┆ …             │\n",
       "│ 2024-05-27 00:00:00 ┆ 15             ┆ 5.0           │\n",
       "│ 2024-06-03 00:00:00 ┆ 12             ┆ 4.0           │\n",
       "│ 2024-06-10 00:00:00 ┆ 17             ┆ 5.666667      │\n",
       "│ 2024-06-17 00:00:00 ┆ 6              ┆ 2.0           │\n",
       "│ 2024-06-24 00:00:00 ┆ 14             ┆ 4.666667      │\n",
       "└─────────────────────┴────────────────┴───────────────┘"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_attendance = pl.read_parquet(\"math_classes.parquet\")\n",
    "\n",
    "(\n",
    "    math_attendance.group_by_dynamic(\n",
    "        index_column=\"class_start\", every=\"1w\", closed=\"both\"\n",
    "    ).agg(\n",
    "        total_absences=pl.col(\"absences\").sum(),\n",
    "        mean_absences=pl.col(\"absences\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "62c3f14e-5e19-4b71-a06c-71b8a45ff3b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (6, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>class_subject</th><th>class_start</th><th>absences</th></tr><tr><td>str</td><td>datetime[μs]</td><td>i64</td></tr></thead><tbody><tr><td>&quot;algebra&quot;</td><td>2024-01-01 00:00:00</td><td>56</td></tr><tr><td>&quot;algebra&quot;</td><td>2024-04-01 00:00:00</td><td>44</td></tr><tr><td>&quot;geometry&quot;</td><td>2024-01-01 00:00:00</td><td>35</td></tr><tr><td>&quot;geometry&quot;</td><td>2024-04-01 00:00:00</td><td>40</td></tr><tr><td>&quot;calculus&quot;</td><td>2024-01-01 00:00:00</td><td>41</td></tr><tr><td>&quot;calculus&quot;</td><td>2024-04-01 00:00:00</td><td>39</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (6, 3)\n",
       "┌───────────────┬─────────────────────┬──────────┐\n",
       "│ class_subject ┆ class_start         ┆ absences │\n",
       "│ ---           ┆ ---                 ┆ ---      │\n",
       "│ str           ┆ datetime[μs]        ┆ i64      │\n",
       "╞═══════════════╪═════════════════════╪══════════╡\n",
       "│ algebra       ┆ 2024-01-01 00:00:00 ┆ 56       │\n",
       "│ algebra       ┆ 2024-04-01 00:00:00 ┆ 44       │\n",
       "│ geometry      ┆ 2024-01-01 00:00:00 ┆ 35       │\n",
       "│ geometry      ┆ 2024-04-01 00:00:00 ┆ 40       │\n",
       "│ calculus      ┆ 2024-01-01 00:00:00 ┆ 41       │\n",
       "│ calculus      ┆ 2024-04-01 00:00:00 ┆ 39       │\n",
       "└───────────────┴─────────────────────┴──────────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "math_attendance = pl.read_parquet(\"math_classes.parquet\")\n",
    "\n",
    "(\n",
    "    math_attendance.group_by_dynamic(\n",
    "        index_column=\"class_start\",\n",
    "        every=\"1q\",\n",
    "        closed=\"both\",\n",
    "        group_by=\"class_subject\",\n",
    "    ).agg(pl.col(\"absences\").sum())\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeea2d1a-5ae8-4ac1-9216-93c9a4e075bf",
   "metadata": {},
   "source": [
    "# Grouping and Aggregating Using Window Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "188f6fbd-6cb9-421c-9d86-7e4bea105130",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1_044, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>school</th><th>student_id</th><th>reason</th><th>absences</th><th>mean_absences</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>str</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10001</td><td>&quot;course&quot;</td><td>6</td><td>4.104839</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10002</td><td>&quot;course&quot;</td><td>4</td><td>4.104839</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10003</td><td>&quot;other&quot;</td><td>10</td><td>6.518519</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10004</td><td>&quot;home&quot;</td><td>2</td><td>7.397959</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10005</td><td>&quot;home&quot;</td><td>4</td><td>7.397959</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10645</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10646</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10647</td><td>&quot;course&quot;</td><td>6</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10648</td><td>&quot;course&quot;</td><td>6</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10649</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1_044, 6)\n",
       "┌─────────┬────────┬────────────┬────────┬──────────┬───────────────┐\n",
       "│ subject ┆ school ┆ student_id ┆ reason ┆ absences ┆ mean_absences │\n",
       "│ ---     ┆ ---    ┆ ---        ┆ ---    ┆ ---      ┆ ---           │\n",
       "│ str     ┆ str    ┆ i64        ┆ str    ┆ i64      ┆ f64           │\n",
       "╞═════════╪════════╪════════════╪════════╪══════════╪═══════════════╡\n",
       "│ M       ┆ GP     ┆ 10001      ┆ course ┆ 6        ┆ 4.104839      │\n",
       "│ M       ┆ GP     ┆ 10002      ┆ course ┆ 4        ┆ 4.104839      │\n",
       "│ M       ┆ GP     ┆ 10003      ┆ other  ┆ 10       ┆ 6.518519      │\n",
       "│ M       ┆ GP     ┆ 10004      ┆ home   ┆ 2        ┆ 7.397959      │\n",
       "│ M       ┆ GP     ┆ 10005      ┆ home   ┆ 4        ┆ 7.397959      │\n",
       "│ …       ┆ …      ┆ …          ┆ …      ┆ …        ┆ …             │\n",
       "│ P       ┆ MS     ┆ 10645      ┆ course ┆ 4        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10646      ┆ course ┆ 4        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10647      ┆ course ┆ 6        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10648      ┆ course ┆ 6        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10649      ┆ course ┆ 4        ┆ 2.627119      │\n",
       "└─────────┴────────┴────────────┴────────┴──────────┴───────────────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "all_students.select(\n",
    "    pl.col(\"subject\", \"school\", \"student_id\", \"reason\", \"absences\"),\n",
    "    mean_absences=(\n",
    "        pl.col(\"absences\").mean().over(\"subject\", \"school\", \"reason\")\n",
    "    ),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "4fedbbbd-2335-461b-a33c-95e0cb89a6a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (381, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>school</th><th>student_id</th><th>reason</th><th>absences</th><th>mean_absences</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>str</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10001</td><td>&quot;course&quot;</td><td>6</td><td>4.104839</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10003</td><td>&quot;other&quot;</td><td>10</td><td>6.518519</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10006</td><td>&quot;reputation&quot;</td><td>10</td><td>6.72</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10019</td><td>&quot;course&quot;</td><td>16</td><td>4.104839</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>10026</td><td>&quot;home&quot;</td><td>14</td><td>7.397959</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10645</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10646</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10647</td><td>&quot;course&quot;</td><td>6</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10648</td><td>&quot;course&quot;</td><td>6</td><td>2.627119</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>10649</td><td>&quot;course&quot;</td><td>4</td><td>2.627119</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (381, 6)\n",
       "┌─────────┬────────┬────────────┬────────────┬──────────┬───────────────┐\n",
       "│ subject ┆ school ┆ student_id ┆ reason     ┆ absences ┆ mean_absences │\n",
       "│ ---     ┆ ---    ┆ ---        ┆ ---        ┆ ---      ┆ ---           │\n",
       "│ str     ┆ str    ┆ i64        ┆ str        ┆ i64      ┆ f64           │\n",
       "╞═════════╪════════╪════════════╪════════════╪══════════╪═══════════════╡\n",
       "│ M       ┆ GP     ┆ 10001      ┆ course     ┆ 6        ┆ 4.104839      │\n",
       "│ M       ┆ GP     ┆ 10003      ┆ other      ┆ 10       ┆ 6.518519      │\n",
       "│ M       ┆ GP     ┆ 10006      ┆ reputation ┆ 10       ┆ 6.72          │\n",
       "│ M       ┆ GP     ┆ 10019      ┆ course     ┆ 16       ┆ 4.104839      │\n",
       "│ M       ┆ GP     ┆ 10026      ┆ home       ┆ 14       ┆ 7.397959      │\n",
       "│ …       ┆ …      ┆ …          ┆ …          ┆ …        ┆ …             │\n",
       "│ P       ┆ MS     ┆ 10645      ┆ course     ┆ 4        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10646      ┆ course     ┆ 4        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10647      ┆ course     ┆ 6        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10648      ┆ course     ┆ 6        ┆ 2.627119      │\n",
       "│ P       ┆ MS     ┆ 10649      ┆ course     ┆ 4        ┆ 2.627119      │\n",
       "└─────────┴────────┴────────────┴────────────┴──────────┴───────────────┘"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "all_students.select(\n",
    "    pl.col(\"subject\", \"school\", \"student_id\", \"reason\", \"absences\"),\n",
    "    mean_absences=(\n",
    "        pl.col(\"absences\").mean().over(\"subject\", \"school\", \"reason\")\n",
    "    ),\n",
    ").filter(pl.col(\"absences\") > pl.col(\"mean_absences\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0becfe6-b7a6-462c-b6cd-5faedfa047fa",
   "metadata": {},
   "source": [
    "# Grouping and Aggregating Using Pivot Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "453d8f2b-ce41-4465-ac94-eb403706de1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (4, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>school</th><th>mean_absence</th><th>mean_failure</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;P&quot;</td><td>&quot;GP&quot;</td><td>4.21513</td><td>0.172577</td></tr><tr><td>&quot;P&quot;</td><td>&quot;MS&quot;</td><td>2.619469</td><td>0.314159</td></tr><tr><td>&quot;M&quot;</td><td>&quot;MS&quot;</td><td>3.76087</td><td>0.456522</td></tr><tr><td>&quot;M&quot;</td><td>&quot;GP&quot;</td><td>5.965616</td><td>0.318052</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (4, 4)\n",
       "┌─────────┬────────┬──────────────┬──────────────┐\n",
       "│ subject ┆ school ┆ mean_absence ┆ mean_failure │\n",
       "│ ---     ┆ ---    ┆ ---          ┆ ---          │\n",
       "│ str     ┆ str    ┆ f64          ┆ f64          │\n",
       "╞═════════╪════════╪══════════════╪══════════════╡\n",
       "│ P       ┆ GP     ┆ 4.21513      ┆ 0.172577     │\n",
       "│ P       ┆ MS     ┆ 2.619469     ┆ 0.314159     │\n",
       "│ M       ┆ MS     ┆ 3.76087      ┆ 0.456522     │\n",
       "│ M       ┆ GP     ┆ 5.965616     ┆ 0.318052     │\n",
       "└─────────┴────────┴──────────────┴──────────────┘"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import polars as pl\n",
    "\n",
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.group_by([\"subject\", \"school\"]).agg(\n",
    "        mean_absence=pl.col(\"absences\").mean(),\n",
    "        mean_failure=pl.col(\"failures\").mean(),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d3cc6cdb-c241-4de3-b39e-93844670dd65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>absences_GP</th><th>absences_MS</th><th>failures_GP</th><th>failures_MS</th></tr><tr><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>5.965616</td><td>3.76087</td><td>0.318052</td><td>0.456522</td></tr><tr><td>&quot;P&quot;</td><td>4.21513</td><td>2.619469</td><td>0.172577</td><td>0.314159</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 5)\n",
       "┌─────────┬─────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ subject ┆ absences_GP ┆ absences_MS ┆ failures_GP ┆ failures_MS │\n",
       "│ ---     ┆ ---         ┆ ---         ┆ ---         ┆ ---         │\n",
       "│ str     ┆ f64         ┆ f64         ┆ f64         ┆ f64         │\n",
       "╞═════════╪═════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ M       ┆ 5.965616    ┆ 3.76087     ┆ 0.318052    ┆ 0.456522    │\n",
       "│ P       ┆ 4.21513     ┆ 2.619469    ┆ 0.172577    ┆ 0.314159    │\n",
       "└─────────┴─────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.pivot(\n",
    "        on=\"school\",\n",
    "        index=\"subject\",\n",
    "        values=[\"absences\", \"failures\"],\n",
    "        aggregate_function=\"mean\",\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7005577f-ff6f-43eb-9f78-a582fe35311b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>absences_GP</th><th>failures_GP</th><th>absences_MS</th><th>failures_MS</th></tr><tr><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>5.965616</td><td>0.318052</td><td>3.76087</td><td>0.456522</td></tr><tr><td>&quot;P&quot;</td><td>4.21513</td><td>0.172577</td><td>2.619469</td><td>0.314159</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 5)\n",
       "┌─────────┬─────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ subject ┆ absences_GP ┆ failures_GP ┆ absences_MS ┆ failures_MS │\n",
       "│ ---     ┆ ---         ┆ ---         ┆ ---         ┆ ---         │\n",
       "│ str     ┆ f64         ┆ f64         ┆ f64         ┆ f64         │\n",
       "╞═════════╪═════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ M       ┆ 5.965616    ┆ 0.318052    ┆ 3.76087     ┆ 0.456522    │\n",
       "│ P       ┆ 4.21513     ┆ 0.172577    ┆ 2.619469    ┆ 0.314159    │\n",
       "└─────────┴─────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "\n",
    "(\n",
    "    all_students.pivot(\n",
    "        on=\"school\",\n",
    "        index=\"subject\",\n",
    "        values=[\"absences\", \"failures\"],\n",
    "        aggregate_function=\"mean\",\n",
    "    ).select(\n",
    "        pl.col(\n",
    "            \"subject\",\n",
    "            \"absences_GP\",\n",
    "            \"failures_GP\",\n",
    "            \"absences_MS\",\n",
    "            \"failures_MS\",\n",
    "        ),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b048a592-3a14-458e-aadb-e4b973119625",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>subject</th><th>reason</th><th>absences_GP</th><th>failures_GP</th><th>absences_MS</th><th>failures_MS</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;M&quot;</td><td>&quot;course&quot;</td><td>4.104839</td><td>0.33871</td><td>3.190476</td><td>0.52381</td></tr><tr><td>&quot;M&quot;</td><td>&quot;other&quot;</td><td>6.518519</td><td>0.333333</td><td>2.888889</td><td>0.222222</td></tr><tr><td>&quot;M&quot;</td><td>&quot;home&quot;</td><td>7.397959</td><td>0.357143</td><td>4.909091</td><td>0.636364</td></tr><tr><td>&quot;M&quot;</td><td>&quot;reputation&quot;</td><td>6.72</td><td>0.25</td><td>5.2</td><td>0.2</td></tr><tr><td>&quot;P&quot;</td><td>&quot;course&quot;</td><td>3.928144</td><td>0.245509</td><td>2.627119</td><td>0.449153</td></tr><tr><td>&quot;P&quot;</td><td>&quot;other&quot;</td><td>3.407407</td><td>0.185185</td><td>2.4</td><td>0.244444</td></tr><tr><td>&quot;P&quot;</td><td>&quot;home&quot;</td><td>4.869565</td><td>0.130435</td><td>3.058824</td><td>0.147059</td></tr><tr><td>&quot;P&quot;</td><td>&quot;reputation&quot;</td><td>4.166667</td><td>0.105263</td><td>2.413793</td><td>0.068966</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 6)\n",
       "┌─────────┬────────────┬─────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ subject ┆ reason     ┆ absences_GP ┆ failures_GP ┆ absences_MS ┆ failures_MS │\n",
       "│ ---     ┆ ---        ┆ ---         ┆ ---         ┆ ---         ┆ ---         │\n",
       "│ str     ┆ str        ┆ f64         ┆ f64         ┆ f64         ┆ f64         │\n",
       "╞═════════╪════════════╪═════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ M       ┆ course     ┆ 4.104839    ┆ 0.33871     ┆ 3.190476    ┆ 0.52381     │\n",
       "│ M       ┆ other      ┆ 6.518519    ┆ 0.333333    ┆ 2.888889    ┆ 0.222222    │\n",
       "│ M       ┆ home       ┆ 7.397959    ┆ 0.357143    ┆ 4.909091    ┆ 0.636364    │\n",
       "│ M       ┆ reputation ┆ 6.72        ┆ 0.25        ┆ 5.2         ┆ 0.2         │\n",
       "│ P       ┆ course     ┆ 3.928144    ┆ 0.245509    ┆ 2.627119    ┆ 0.449153    │\n",
       "│ P       ┆ other      ┆ 3.407407    ┆ 0.185185    ┆ 2.4         ┆ 0.244444    │\n",
       "│ P       ┆ home       ┆ 4.869565    ┆ 0.130435    ┆ 3.058824    ┆ 0.147059    │\n",
       "│ P       ┆ reputation ┆ 4.166667    ┆ 0.105263    ┆ 2.413793    ┆ 0.068966    │\n",
       "└─────────┴────────────┴─────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_students = pl.read_parquet(\"course.parquet\")\n",
    "(\n",
    "    all_students.pivot(\n",
    "        on=\"school\",\n",
    "        index=[\"subject\", \"reason\"],\n",
    "        values=[\"absences\", \"failures\"],\n",
    "        aggregate_function=\"mean\",\n",
    "    ).select(\n",
    "        pl.col(\n",
    "            \"subject\",\n",
    "            \"reason\",\n",
    "            \"absences_GP\",\n",
    "            \"failures_GP\",\n",
    "            \"absences_MS\",\n",
    "            \"failures_MS\",\n",
    "        ),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f88687d-a54d-45ef-8d37-e2fd23e41786",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
